﻿CREATE PROCEDURE [dbo].[FinancialContractDetailReport]
	@FromDate datetime,
	@EndDate datetime,
	@ProjectId int
WITH RECOMPILE 
AS
BEGIN
	SET @EndDate = DateAdd(day, 1, @EndDate);
	WITH 
	[EntranceView]
	AS
	(
		SELECT	[Id],
						[BuildingId], 
						[Name]
		FROM		[dbo].[Entrance]
		WHERE	[IsDeleted] = 0
	),
	[FloorView]
	AS
	(
		SELECT	[Id],
						[BuildingId], 
						[Name]
		FROM		[dbo].[Floor]
		WHERE	[IsDeleted] = 0
	),
	[BuildingView]
	AS
	(
		SELECT	[Id],
						[ProjectId], 
						[Name]
		FROM		[dbo].[Building]
		WHERE	[IsDeleted] = 0
	),
	[ProjectView]
	AS
	(
		SELECT	[Id], 
						[Name]
		FROM		[dbo].[Project]
		WHERE	[IsDeleted] = 0 AND [Id] = @ProjectId
	),
	[EmployeeView]
	AS
	(
		SELECT	[Id],
						[Name]
		FROM		[dbo].[Employee]
		WHERE	[IsDeleted] = 0 AND [ProjectId] = @ProjectId
	),
	[CustomerView]
	AS
	(
		SELECT	[Id],
						[Name],
						[Address], 
						[IdentityNumber], 
						[Mobile]
		FROM		[dbo].[Customer]
		WHERE	[IsDeleted] = 0 AND [ProjectId] = @ProjectId
	),
	[PaymentTypeView]
	AS
	(
		SELECT	[Id], 
						[Name]
		FROM		[dbo].[PaymentType]
		WHERE	[IsDeleted] = 0
	),
	[AgreementView]
	AS
	(
		SELECT	[Id],
						[RoomId],
						[CustomerId],
						[SalesEmployeeId]
		FROM		[dbo].[Agreement]
		WHERE	[IsDeleted] = 0
	),
	[LargeOrderView]
	AS
	(
		SELECT	[Id], 
						[AgreementId],
						[DepositAmount], 
						[SignatureDate]
		FROM		[dbo].[LargeOrder]
		WHERE	[IsDeleted] = 0 AND [SignatureDate] < @EndDate
	),
	[ContractReceiptView]
	AS
	(
		SELECT	CR.[Id], 
						CR.[ContractId], 
						CR.[ReceiptTypeId], 
						CR.[ScheduledDate], 
						CR.[ReceivableAmount], 
						ISNULL(CRR.[ReceivedAmount], 0) AS [ReceivedAmount], 
						ROW_NUMBER() OVER (PARTITION BY [ContractId] ORDER BY [ScheduledDate] ASC) AS [RowNumber]
		FROM		[dbo].[ContractReceipt] AS CR
			LEFT OUTER JOIN
			(
				SELECT	[ContractReceiptId],
								SUM([ReceivedAmount]) AS [ReceivedAmount]
				FROM		[dbo].[ContractReceivedReceipt]
				WHERE	[IsDeleted] = 0 AND [ReceivedDate] < @EndDate
				GROUP BY
								[ContractReceiptId]
			) AS CRR 	ON CR.[Id] = CRR.[ContractReceiptId]
		WHERE	CR.[IsDeleted] = 0
	),
	[ContractView]
	AS
	(
		SELECT	C.[Id], 
						C.[AgreementId],
						C.[ContractNumber], 
						C.[ReducedUnitPrice], 
						C.[ReducedTotalPrice], 
						PT.[Name] AS [PaymentType], 
						C.[SignatureDate], 
						C.[TotalAmount], 
						ISNULL(CR.[ReceivedAmount1], 0) AS [ReceivableAmount1], 
						CR.[ScheduledDate1], 
						ISNULL(CR.[ReceivedAmount2], 0) AS [ReceivableAmount2], 
						CR.[ScheduledDate2], 
						ISNULL(CR.[ReceivedAmount3], 0) AS [ReceivableAmount3], 
						CR.[ScheduledDate3], 
						(ISNULL(CR.[ReceivableAmountOther1], 0) - ISNULL(CR.[ReceivedAmountOther1], 0)) AS [ReceivableAmountOther1], 
						(ISNULL(CR.[ReceivableAmountOther2], 0) - ISNULL(CR.[ReceivedAmountOther2], 0)) AS [ReceivableAmountOther2], 
						ISNULL(CR.[ReceivableAmount], 0) AS [ReceivableAmount], 
						ISNULL(CR.[ReceivedAmount], 0) AS [ReceivedAmount], 
						CASE WHEN ISNULL(CR.[ReceivableAmount], 0) = ISNULL(CR.[ReceivedAmount], 0) THEN 1 ELSE 0 END AS [Received], 
						C.[Comment]
		FROM		[dbo].[Contract] AS C
			INNER JOIN [PaymentTypeView] AS PT ON C.[PaymentTypeId] = PT.[Id] 
			LEFT OUTER JOIN
			(
				SELECT	CRV.[ContractId],
								(SELECT TOP 1 [ScheduledDate] FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [RowNumber] = 1) AS [ScheduledDate1],
								(SELECT TOP 1 [ReceivedAmount] FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [RowNumber] = 1) AS [ReceivedAmount1],
								(SELECT TOP 1 [ScheduledDate] FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [RowNumber] = 2) AS [ScheduledDate2],
								(SELECT TOP 1 [ReceivedAmount] FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [RowNumber] = 2) AS [ReceivedAmount2],
								(SELECT TOP 1 [ScheduledDate] FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [RowNumber] = 3) AS [ScheduledDate3],
								(SELECT TOP 1 [ReceivedAmount] FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [RowNumber] = 3) AS [ReceivedAmount3],
								(SELECT SUM([ReceivableAmount]) FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [ReceiptTypeId] IN (1, 2)) AS [ReceivableAmountOther1],
								(SELECT SUM([ReceivedAmount]) FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [ReceiptTypeId] IN (1, 2)) AS [ReceivedAmountOther1],
								(SELECT SUM([ReceivableAmount]) FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [ReceiptTypeId] IN (3, 4)) AS [ReceivableAmountOther2],
								(SELECT SUM([ReceivedAmount]) FROM [ContractReceiptView] WHERE [ContractId] = CRV.[ContractId] AND [ReceiptTypeId] IN (3, 4)) AS [ReceivedAmountOther2],
								SUM(CRV.[ReceivableAmount]) AS [ReceivableAmount],
								SUM(CRV.[ReceivedAmount]) AS [ReceivedAmount]
				FROM		[ContractReceiptView] AS CRV
				GROUP BY	
								CRV.[ContractId]
			) AS CR
			ON C.[Id] = CR.[ContractId]
		WHERE	C.[IsDeleted] = 0 AND C.[SignatureDate] >= @FromDate AND C.[SignatureDate] < @EndDate
	)
	SELECT	P.[Id] AS [ProjectId], 
					B.[Id] AS [BuildingId],
					E.[Id] AS [EntranceId],
					F.[Id] AS [FloorId],
					R.[Id] AS [RoomId],
					R.[Name] AS [RoomName], 
					R.[SaleArea],
					AG.[Id] AS [AgreementId], 
					CT.[Id] AS [CustomerId], 
					CT.[Name] AS [CustomerName], 
					CT.[Address] AS [CustomerAddress], 
					CT.[IdentityNumber] AS [CustomerIdentityNumber], 
					CT.[Mobile] AS [CustomerMobile], 
					EP.[Id] AS [EmployeeId], 
					EP.[Name] AS [EmployeeName], 
					LO.[Id] AS [LargeOrderId], 
					LO.[SignatureDate] AS [LargeOrderSignatureDate], 
					LO.[DepositAmount] AS [LargeOrderDepositAmount], 
					C.[Id] AS [ContractId], 
					C.[ContractNumber], 
					C.[SignatureDate] AS [ContractSignatureDate], 
					C.[ReducedUnitPrice] AS [ContractUnitPrice], 
					C.[ReducedTotalPrice] AS [ContractTotalPrice], 
					C.[PaymentType] AS [ContractPaymentType], 
					C.[TotalAmount], 
					C.[ReceivableAmount1], 
					C.[ScheduledDate1], 
					C.[ReceivableAmount2], 
					C.[ScheduledDate2], 
					C.[ReceivableAmount3], 
					C.[ScheduledDate3], 
					C.[ReceivableAmountOther1], 
					C.[ReceivableAmountOther2], 
					C.[Received], 
					C.[Comment]
	FROM		[dbo].[Room] AS R 
		INNER JOIN [EntranceView] AS E ON R.[EntranceId] = E.[Id]
		INNER JOIN [FloorView] AS F ON R.[FloorId] = F.[Id]
		INNER JOIN [BuildingView] AS B ON E.[BuildingId] = B.[Id]
		INNER JOIN [ProjectView] AS P ON B.[ProjectId] = P.[Id]
		INNER JOIN [AgreementView] AS AG ON R.[ActiveAgreementId] = AG.[Id]
		INNER JOIN [CustomerView] AS CT ON AG.[CustomerId] = CT.[Id] 
		INNER JOIN [EmployeeView] AS EP ON AG.[SalesEmployeeId] = EP.[Id] 
		LEFT OUTER JOIN [LargeOrderView] AS LO ON AG.[Id] = LO.[AgreementId]
		LEFT OUTER JOIN [ContractView] AS C ON AG.[Id] = C.[AgreementId]
	WHERE R.[IsDeleted] = 0 AND (R.[RoomStateId] = 5 OR R.[RoomStateId] = 6)
END
